package com.zizo.autohome.emotion.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.hankcs.hanlp.seg.common.Term;
import com.zizo.autohome.emotion.alg.AhoCorasickDoubleArrayTrie;
import com.zizo.autohome.emotion.bean.Sentence;
import com.zizo.autohome.emotion.bean.Topic;
import com.zizo.autohome.emotion.util.TopicUtil;
import com.zizo.util.ConnectionPool;
import com.zizo.util.Util;

public class TopicDao implements Runnable {
	static final float NOSCORE = 101;// 无分数的默认分数为101，插入时做判断
	private HashMap<Integer, String[]> tagMap;// 词语的Map集合
	private HashMap<Integer, String[]> topicMap;// 主题的Map集合
	private HashMap<Integer, Float> scoreMap;// 分数的Map集合
	private Map<String, Float> advMap;// 副词的Map表
	private Connection conn168;
	private Connection conn198;
	private String tableName;// 存储的表名称
	private String table;// 要分析的表名称
	private AhoCorasickDoubleArrayTrie<List<Integer>> acdatLocal;

	@Override
	public void run() {
		alterTable();
		getSentence(table);
		System.out.println(table);
	}

	/**
	 * @Title: alterTable
	 * @Description: 创建新表，删除旧表
	 * @return void
	 * @author Alexis
	 */
	public void alterTable() {
		Statement st = null;
		String sqlA = "drop table if EXISTS topic_auto_" + tableName;
		String sql = "create table topic_auto_" + tableName + " like topic_auto";
		try {
			st = conn198.createStatement();
			st.executeUpdate(sqlA);
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Title: getSentence
	 * @Description: 根据表名称得到语料数据
	 * @param tablename
	 * @return void
	 * @author Alexis
	 */
	public List<Sentence> getSentence(String tablename) {
		Statement st = null;
		ResultSet rs = null;
		if (tablename.contains("kb")) {
			String sql = "select kb_kbid,kb_cid,kb_mid,kb_ptime,kb_province,kb_area,kb_content from autohome_kb";
			try {
				st = conn168.createStatement();
				rs = st.executeQuery(sql);
				while (rs.next()) {
					int kb_kbid = rs.getInt(1);
					int kb_cid = rs.getInt(2);
					int kb_mid = rs.getInt(3);
					String kb_ptime = rs.getString(4);
					String kb_province = rs.getString(5);
					String kb_content = rs.getString(7);
					String[] sentences = kb_content.split("\\}\\{|\\?|\n|【");
					int length = sentences.length;
					for (int i = 0; i < length; i++) {
						Sentence sen = new Sentence(kb_kbid, kb_cid, kb_mid, kb_ptime, getProvince(kb_province),
								kb_province, sentences[i], i);
						modifySentence(sen, "kb");
						sen = null;
					}
					sentences = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} else if (tablename.contains("bbs")) {
			String bbsid = tablename.split("_")[2];
			int cid = getKBCID(bbsid);
			String sql = "select bbs_bbsid,bbs_cid,bbs_title,bbs_content,bbs_ptime,bbs_sf,bbs_cs from " + tablename;
			try {
				st = conn168.createStatement();
				rs = st.executeQuery(sql);
				while (rs.next()) {
					int bbs_bbsid = rs.getInt(1);
					String bbs_title = rs.getString(3);
					String bbs_content = rs.getString(4);
					String bbs_ptime = rs.getString(5);
					String bbs_province = rs.getString(6);
					String bbs_city = rs.getString(7);
					String[] sentences_title = bbs_title.split("\\}\\{|\\?|\n|【");
					String[] sentences_content = bbs_content.split("\\}\\{|\\?|\n|【");
					int index = 0;
					int titleLength = sentences_title.length;
					for (int i = 0; i < titleLength; i++) {
						Sentence sen = new Sentence(bbs_bbsid, cid, 0, bbs_ptime, bbs_province, bbs_city,
								sentences_title[i], index);
						index++;
						modifySentence(sen, "bbs");
						sen = null;
					}
					int contentLength = sentences_content.length;
					for (int i = 0; i < contentLength; i++) {
						Sentence sen = new Sentence(bbs_bbsid, cid, 0, bbs_ptime, bbs_province, bbs_city,
								sentences_content[i], index);
						index++;
						modifySentence(sen, "bbs");
						sen = null;
					}
					sentences_title = null;
					sentences_content = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					rs.close();
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	/**
	 * @Title: getProvince
	 * @Description: 根据给出的城市名称得到省份
	 * @param city
	 * @return
	 * @return String
	 * @author Alexis
	 */
	public String getProvince(String city) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT area_province FROM carvoice_core.core_area WHERE area_city='" + city + "' LIMIT 1";
		String pro = "";
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				pro = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return pro;
	}

	/**
	 * @Title: getKBCID
	 * @Description: 根据论坛的车型ID得到口碑的车型ID
	 * @param bbsid
	 * @return
	 * @return int
	 * @author Alexis
	 */
	public int getKBCID(String bbsid) {
		int carid = 0;
		Statement st = null;
		ResultSet rs = null;
		String sql = "select brand_autohomeId from carvoice_core.core_brand where brand_autohomeClubId=" + bbsid;
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				carid = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return carid;
	}

	/**
	 * @Title: insertSentence
	 * @Description: 插入
	 * @param sen
	 * @param top
	 * @param type
	 *            KB&&BBS
	 * @return void
	 * @author Alexis
	 */
	public void insertSentence(Sentence sen, Topic top, String type) {
		PreparedStatement ps = null;
		if (top.getScore() != 101) {
			String sql = "insert into topic_auto_" + tableName
					+ "(topic_postid,topic_sentenceid,topic_cid,topic_mid,topic_ptime,topic_sentence,topic_adv,topic_t0,topic_t1,topic_t2,topic_t3,topic_t4,topic_score,topic_emotion,topic_emoLevel,topic_province,topic_city,topic_source) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,ROUND(?,0),?,?,?)";
			try {
				ps = conn198.prepareStatement(sql);
				ps.setInt(1, sen.getBid());
				ps.setInt(2, sen.getIndex());
				ps.setInt(3, sen.getCid());
				ps.setInt(4, sen.getMid());
				ps.setDate(5, (Date) Util.getDate(sen.getPtime()));
				ps.setString(6, sen.getSentence());
				ps.setString(7, top.getAdv());
				ps.setString(8, top.getTopic0());
				ps.setString(9, top.getTopic1());
				ps.setString(10, top.getTopic2());
				ps.setString(11, top.getTopic3());
				ps.setString(12, top.getTopic4());
				ps.setFloat(13, top.getScore());
				ps.setFloat(14, top.getEmotion());
				float emotion = top.getEmotion();
				int res = 0;
				if (emotion >= 5) {
					res = 1;
				} else if (emotion >= 3 && emotion < 5) {
					res = 2;
				} else if (emotion > 0 && emotion < 3) {
					res = 3;
				} else if (emotion == 0) {
					res = 4;
				} else if (emotion > -3 && emotion < 0) {
					res = 5;
				} else if (emotion > -5 && emotion <= -3) {
					res = 6;
				} else if (emotion <= -5) {
					res = 7;
				}
				ps.setFloat(15, res);
				ps.setString(16, sen.getProvince());
				ps.setString(17, sen.getArea());
				ps.setString(18, type);
				ps.executeUpdate();
				sen = null;
				top = null;
				type = null;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} else {
			String sql = "insert into topic_auto_" + tableName
					+ "(topic_postid,topic_sentenceid,topic_cid,topic_mid,topic_ptime,topic_sentence,topic_adv,topic_t0,topic_t1,topic_t2,topic_t3,topic_t4,topic_province,topic_city,topic_source) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			try {
				ps = conn198.prepareStatement(sql);
				ps.setInt(1, sen.getBid());
				ps.setInt(2, sen.getIndex());
				ps.setInt(3, sen.getCid());
				ps.setInt(4, sen.getMid());
				ps.setDate(5, (Date) Util.getDate(sen.getPtime()));
				ps.setString(6, sen.getSentence());
				ps.setString(7, top.getAdv());
				ps.setString(8, top.getTopic0());
				ps.setString(9, top.getTopic1());
				ps.setString(10, top.getTopic2());
				ps.setString(11, top.getTopic3());
				ps.setString(12, top.getTopic4());
				ps.setString(13, sen.getProvince());
				ps.setString(14, sen.getArea());
				ps.setString(15, type);
				ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public TopicDao(HashMap<Integer, String[]> tagMap, HashMap<Integer, String[]> topicMap,
			HashMap<Integer, Float> scoreMap, Map<String, Float> advMap, Connection conn168, Connection conn198,
			String tableName, String table, AhoCorasickDoubleArrayTrie<List<Integer>> acdatLocal) {
		super();
		this.tagMap = tagMap;
		this.topicMap = topicMap;
		this.scoreMap = scoreMap;
		this.advMap = advMap;
		this.conn168 = conn168;
		this.conn198 = conn198;
		this.tableName = tableName;
		this.table = table;
		this.acdatLocal = acdatLocal;
	}

	/**
	 * @Title: modifySentence
	 * @Description: 判定给定的句子是否包含给定的关键词
	 * @param sentence
	 * @return void
	 * @author Alexis
	 */
	public void modifySentence(Sentence sentence, String type) {
		/* 找词部分 */
		List<Integer> hitTopicList = findWord(sentence.getSentence());
		if (hitTopicList == null || hitTopicList.size() == 0) {
			hitTopicList = null;
			return;
		}
		/* 主题部分 */
		List<Topic> hit = new ArrayList<>();
		for (int i : hitTopicList) {
			String[] tags = tagMap.get(i);
			String[] topics = topicMap.get(i);
			Topic t = new Topic(tags[0], tags[1], tags[2], tags[3], topics[0], topics[1], topics[2], topics[3],
					topics[4], scoreMap.get(i));
			hit.add(t);
		}
		hitTopicList = null;
		List<Topic> finalTopicList = combinatTopic(hit);
		/* 副词部分 */
		Map<Float, List<String>> advMap = getAdvAvg(sentence.getSentence());
		float advAvg = 0;
		List<String> advList = null;
		for (float f : advMap.keySet()) {
			advAvg = f;
			advList = advMap.get(f);
		}
		StringBuilder sb = new StringBuilder();
		sb.append("[");
		int advListSize = advList.size();
		for (int i = 0; i < advListSize; i++) {
			if (i != advListSize - 1) {
				sb.append(advList.get(i) + ",");
			} else {
				sb.append(advList.get(i));
			}

		}
		sb.append("]");
		for (int i = 0; i < finalTopicList.size(); i++) {
			Topic topic = finalTopicList.get(i);
			if (topic.getScore() != NOSCORE) {
				topic.setEmotion(topic.getScore() * advAvg);
				finalTopicList.set(i, topic);
			}
		}
		for (Topic top : finalTopicList) {
			top.setAdv(sb.toString());
			insertSentence(sentence, top, type);
		}
		hitTopicList = null;
		hit = null;
		finalTopicList = null;
		advMap = null;
		advList = null;
	}

	/**
	 * @Title: findWord
	 * @Description: 查找命中的Topic，返回的List<Topic> 为已去重的
	 * @param sentence
	 * @return
	 * @return List<Topic>
	 * @author Alexis
	 */
	public List<Integer> findWord(String sentence) {
		List<List<Integer>> isContains = acdatLocal.parseTextNotHit(sentence);
		if (isContains.size() == 0) {
			isContains = null;
			return null;
		}
		List<Integer> hitTopicList = new ArrayList<>();// 命中的Topic集合ID
		Set<Integer> topicSet = new HashSet<>();
		for (List<Integer> i : isContains) {
			for (Integer t : i) {
				topicSet.add(t);
			}
		}
		isContains = null;
		for (Integer i : topicSet) {
			String[] tags = tagMap.get(i);
			if (sentence.contains(tags[0]) && sentence.contains(tags[1]) && sentence.contains(tags[2])
					&& sentence.contains(tags[3])) {
				hitTopicList.add(i);
			}
			tags = null;
		}
		if (hitTopicList.size() == 0) {
			return null;
		}
		topicSet = null;
		/* 对hitTopicList做去除子集操作 */
		while (true) {
			int size = hitTopicList.size();
			if (size == 1) {
				break;
			}
			int flag = 0;
			for (int i = 0; i < size; i++) {
				for (int j = i + 1; j < size; j++) {
					int topic = compareTopic(hitTopicList.get(i), hitTopicList.get(j));
					/* 如果有子集，则remove子集 */
					if (topic != -1) {
						hitTopicList.remove((Integer) topic);
						flag = 1;
						break;
					} else {
						if (j == size - 1) {
							flag = 2;
							break;
						}
					}
				}
				if (flag == 1) {
					break;
				}
			}
			if (flag == 2) {
				break;
			}
		}
		return hitTopicList;
	}

	/**
	 * @Title: compareTopic
	 * @Description: 判断给定的俩个Topic的关键词长度大小，同时返回俩个Topic中的子集，不是子集关系则返回Null
	 * @param t1
	 * @param t2
	 * @return
	 * @return Topic[]
	 * @author Alexis
	 */
	public int compareTopic(int t1, int t2) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		String[] tp1 = tagMap.get((Integer) t1);
		String[] tp2 = tagMap.get((Integer) t2);
		// 将tag封装到StringBuilder里，方便比较大小
		for (int i = 0; i < 4; i++) {
			sb1.append(tp1[i]);
			sb2.append(tp2[i]);
		}
		int min = t1;
		String maxS = sb2.toString();
		String[] minS = tp1;
		if (sb1.length() > sb2.length()) {
			min = t2;
			maxS = sb1.toString();
			minS = tp2;
		}
		sb1 = null;
		sb2 = null;
		boolean flag = true;

		for (String s : minS) {
			if (s == null || s.length() < 1) {
				continue;
			}
			flag = maxS.contains(s) ? true : false;
			if (!flag) {
				break;
			}
		}
		maxS = null;
		minS = null;
		tp1 = null;
		/* IF flag==True 则证明 topics[1]为topics[0]的子集 */
		if (flag) {
			return min;
		}
		// 否则返回NULL,证明俩个Topic不是子集关系
		return -1;
	}

	/**
	 * @Title: combinatTopic
	 * @Description: 对已经命中的Topic集合做主题合拼处理
	 * @param hitTopicList
	 * @return void
	 * @author Alexis
	 */
	public List<Topic> combinatTopic(List<Topic> hitTopicList) {
		List<Topic> handedTopicList = null;
		while (true) {
			int flag = 0;
			int size = hitTopicList.size();
			if (size == 1) {
				break;
			}
			for (int i = 0; i < size; i++) {
				for (int j = i + 1; j < size; j++) {
					Topic t1 = hitTopicList.get(i);
					Topic t2 = hitTopicList.get(j);
					Topic topic = modifyTopic(t1, t2);
					if (topic != null) {
						hitTopicList.remove(t1);
						hitTopicList.remove(t2);
						hitTopicList.add(topic);
						flag = 1;
						break;
					} else {
						if (i == size - 2) {
							flag = 2;
						}
						continue;
					}
				}
				if (flag == 1) {
					break;
				}
			}
			if (flag == 2) {
				break;
			}
		}
		return handedTopicList == null ? hitTopicList : handedTopicList;
	}

	/**
	 * @Title: modifyTopic
	 * @Description: 对给定的俩个Topic做主题合拼处理,如果可以合拼，返回合拼后的Topic，否则返回NULL
	 * @param topic1
	 * @param topic2
	 * @return
	 * @return Topic
	 * @author Alexis
	 */
	public Topic modifyTopic(Topic topic1, Topic topic2) {
		String[] ts1 = topic1.getTopicList();
		String[] ts2 = topic2.getTopicList();
		List<String> list = new ArrayList<>();
		for (int i = 0; i < 5; i++) {
			if (ts1[i].equals(ts2[i])) {
				list.add(ts1[i]);
			} else if (ts1[i].length() == 0 || ts2[i].length() == 0) {
				list.add(ts1[i] + ts2[i]);
			} else {
				ts1 = null;
				ts2 = null;
				return null;
			}
		}
		ts1 = null;
		ts2 = null;
		float score = 0;
		if (topic1.getScore() == NOSCORE && topic2.getScore() == NOSCORE) {
			score = NOSCORE;
		} else if (topic1.getScore() != NOSCORE && topic2.getScore() == NOSCORE) {
			score = topic1.getScore();
		} else if (topic2.getScore() != NOSCORE && topic1.getScore() == NOSCORE) {
			score = topic2.getScore();
		} else if (Math.abs(topic1.getScore()) != Math.abs(topic2.getScore())) {
			score = Math.abs(topic1.getScore()) > Math.abs(topic2.getScore()) ? topic1.getScore() : topic2.getScore();
		} else if (Math.abs(topic1.getScore()) == Math.abs(topic2.getScore())) {
			score = topic1.getScore() == topic2.getScore() ? topic1.getScore() : 0;
		}
		return new Topic("", "", "", "", list.get(0), list.get(1), list.get(2), list.get(3), list.get(4), score);
	}

	/**
	 * @Title: getAdvAvg
	 * @Description: 计算副词的情感度平均值
	 * @param sentence
	 * @return
	 * @return float
	 * @author Alexis
	 */
	public Map<Float, List<String>> getAdvAvg(String sentence) {
		Map<Float, List<String>> map = new HashMap<>();
		List<Term> termList = TopicUtil.getTermList(sentence);// 语句分词
		int gt0Cnt = 0;// 副词的Score为正数的次数
		int lt0Cnt = 0;
		float gt0Sum = 0;// 副词的Score为正数的总和
		float lt0Sum = 0;
		List<String> adv = new ArrayList<>();
		for (Term term : termList) {
			for (Map.Entry<String, Float> entry : advMap.entrySet()) {
				if (entry.getKey().equals(term.word)) {
					adv.add(term.word);
					if (entry.getValue() >= 0) {
						gt0Cnt++;
						gt0Sum += entry.getValue();
					} else {
						lt0Cnt++;
						lt0Sum += entry.getValue();
					}
				}
			}
		}
		termList = null;
		float gtAvg = 1;
		float ltAvg = 1;
		if (gt0Cnt > 0) {
			gtAvg = gt0Sum / (float) gt0Cnt;
		}
		if (lt0Cnt > 0) {
			ltAvg = lt0Sum / (float) lt0Cnt;
		}
		float advAvg = gtAvg * ltAvg;// 副词的
		map.put(advAvg, adv);
		return map;
	}

	public List<Integer> getBizClubID() {
		List<Integer> list = new ArrayList<>();
		Connection conn = ConnectionPool.getConnection("carvoice_biz", "192.169.0.168");
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT DISTINCT autohome_autohomeClubId FROM carvoice_core.core_brand_autohome WHERE autohome_autohomeId in (SELECT brand_autohome FROM carvoice_biz.biz_brand)";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				list.add(rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public TopicDao() {
		super();
	}

	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		Connection conn168 = ConnectionPool.getConnection("autohome_syn", "192.169.0.168");
		Connection conn198 = ConnectionPool.getConnection("topic_1225", "localhost");
		TopicUtil util = new TopicUtil();
		Map<String, Float> adv = util.getAvd();
		System.out.println("Get Avd GG " + adv.size());

		List<Topic> topicList = util.getTopic("/var/server/java/heyan/topic.txt");// topic的服务器位置
		HashMap<Integer, String[]> tagMap = new HashMap<>();// 词语的Map集合
		HashMap<Integer, String[]> topicMap = new HashMap<>();// 主题的Map集合
		HashMap<Integer, Float> scoreMap = new HashMap<>();// 分数的Map集合
		HashMap<String, List<Integer>> wordMap = new HashMap<>();// 去除重复的Tag集合
		int i = 0;
		for (Topic top : topicList) {
			String[] tags = top.getTagList();
			String[] topics = top.getTopicList();
			tagMap.put(i, tags);
			topicMap.put(i, topics);
			scoreMap.put(i, top.getScore());
			for (String s : tags) {
				if (s == null || s.length() < 1) {
					continue;
				}
				if (wordMap.containsKey(s)) {
					List<Integer> list = wordMap.get(s);
					list.add(i);
					wordMap.put(s, list);
				} else {
					List<Integer> list = new ArrayList<>();
					list.add(i);
					wordMap.put(s, list);
				}
			}
			i++;
		}
		AhoCorasickDoubleArrayTrie<List<Integer>> acdatLocal = new AhoCorasickDoubleArrayTrie<List<Integer>>();
		acdatLocal.build(wordMap);
		System.out.println("Get TopicMAP GG " + tagMap.size());
		ExecutorService fix = Executors.newFixedThreadPool(20);
		List<Integer> clubIDs = new TopicDao().getBizClubID();
		for (int clubid : clubIDs) {
			String saveTableName = "bbs_" + clubid;// 要保存的表名称
			String tableName = "bbs_c_" + clubid;// 分析的表名称
			TopicDao dao = new TopicDao(tagMap, topicMap, scoreMap, adv, conn168, conn198, saveTableName, tableName,
					acdatLocal);
			fix.execute(dao);
		}
		String saveTableName = "kb";// 要保存的表名称
		String tableName = "autohome_kb";// 分析的表名称
		TopicDao dao = new TopicDao(tagMap, topicMap, scoreMap, adv, conn168, conn198, saveTableName, tableName,
				acdatLocal);
		fix.execute(dao);
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				System.out.println("Finish~");
				System.out.println((System.currentTimeMillis() - start) / (1000 * 60) + "Minute");
				break;
			} else {
				try {
					Thread.sleep(1000 * 60 * 10);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}

		}
	}
}
